from __future__ import annotations
from typing import Dict, List, Any

import time
import datetime

import KBEngine
from KBEDebug import DEBUG_MSG, ERROR_MSG


class DataType:
    def __init__(self, filedType: str, notNull: bool, defaultVal: str = ''):
        self.filedType = filedType
        self.notNull = notNull
        self.defaultVal = defaultVal

        if 'int' in filedType:
            self.convertFunc = self.toInt
        elif 'float' in filedType or 'double' in filedType:
            self.convertFun = self.toFloat
        elif 'varchar' in filedType:
            self.convertFunc = self.toStr
            self.valToStr = self.strToStr
        else:
            self.convertFunc = self.toBlob

    def __str__(self):
        if self.notNull:
            return '%s not null default %s' % (self.filedType, self.defaultVal)
        else:
            return self.filedType

    # region 类型转换方法
    def toInt(self, val: bytes):
        return int(val)

    def toStr(self, val: bytes):
        return str(val, 'utf-8')

    def toFloat(self, val: bytes):
        return float(val)

    def toBlob(self, val: bytes):
        return val
    # endregion

    def valToStr(self, val: int | float | bytes):
        return str(val)

    def strToStr(self, val: str):
        val = val.replace('"', '\"').replace("'", '\"')
        return f"'{val}'"

# region 数据类型


def INT8(defaultVal=0) -> DataType:
    return DataType('tinyint(4)', True, defaultVal)


def INT16(defaultVal=0) -> DataType:
    return DataType('smallint(6)', True, defaultVal)


def INT32(defaultVal=0) -> DataType:
    return DataType('int(11)', True, defaultVal)


def INT64(defaultVal=0) -> DataType:
    return DataType('bigint(20)', True, defaultVal)


def UINT8(defaultVal=0) -> DataType:
    return DataType('tinyint(3) unsigned', True, defaultVal)


def UINT16(defaultVal=0) -> DataType:
    return DataType('smallint(5) unsigned', True, defaultVal)


def UINT32(defaultVal=0) -> DataType:
    return DataType('int(10) unsigned', True, defaultVal)


def UINT64(defaultVal=0) -> DataType:
    return DataType('bigint(20) unsigned', True, defaultVal)


def FLOAT(defaultVal=0) -> DataType:
    return DataType('float', True, defaultVal)


def DOUBLE(defaultVal=0) -> DataType:
    return DataType('double', True, defaultVal)


def STRING(length=0, defaultVal='""') -> DataType:
    return DataType('varchar(%i)' % length, True, defaultVal)


def UNICODE(length=0, defaultVal='""') -> DataType:
    return DataType('varchar(%i)' % length, True, defaultVal)


def BLOB() -> DataType:
    return DataType('blob', False)

# endregion


class DBTable(dict):
    """
    功能：自动创建表、删除字段、增加字段、修改字段类型
    注意：表名不能以tbl_或者kbe_开头
    """

    def __init__(self, tbName: str):
        """
        tbName: 表名，不能以tbl_或者kbe_开头
        """
        self.tbName = tbName
        # 表字段
        self.filedDict: Dict[str, DataType] = {
            'id': INT32()
        }
        self.fieldKeys: List[str] = None
        self.fieldKeysStr: str = None
        self.fieldKeysWithoutId: List[str] = []
        self.fieldKeysStrWithoutId: str = None
        # 新增的字段
        self.newFileds: Dict[str, DataType] = {}
        # 要删除的字段
        self.delFileds: List[str] = []
        # 更新类型的字段
        self.excFileds: Dict[str, DataType] = {}
        self.fields: List[str] = []

    # region sql语句
    @property
    def sql(self):
        # id不需要用bigint，存不了那么多数据
        sql = 'create table if not exists %s(id bigint(20) unsigned AUTO_INCREMENT, PRIMARY KEY idKey (id),' % self.tbName

        for filedName in self.fieldKeysWithoutId:
            sql += ' %s %s,' % (filedName, self.filedDict[filedName])

        sql = sql[:-1] + ') ENGINE = InnoDB;'

        return sql

    @property
    def newFiledSql(self):
        if len(self.newFileds) <= 0:
            return ''

        sql = 'alter table %s add(' % self.tbName
        for filedName in self.newFileds:
            sql += ' %s %s,' % (filedName, self.filedDict[filedName])

        sql = sql[:-1] + ');'

        return sql

    @property
    def delFiledSql(self):
        if len(self.delFileds) <= 0:
            return ''

        sql = 'alter table %s' % self.tbName
        for filedName in self.delFileds:
            sql += ' drop column %s,' % filedName

        sql = sql[:-1] + ';'
        return sql

    @property
    def chaFiledSql(self):
        if len(self.excFileds) <= 0:
            return ''

        sql = 'alter table %s' % self.tbName
        for filedName in self.excFileds:
            sql += ' modify %s %s,' % (filedName, self.excFileds[filedName])

        sql = sql[:-1] + ';'

        return sql

    # endregion

    def setFiled(self, filedName: str, filedType: DataType):
        """
        设置字段类型
        """
        if filedName == 'id':
            ERROR_MSG('setFiled error can not set filedName=id')
            return
        self.filedDict[filedName] = filedType
        self.newFileds[filedName] = filedType
        self.fieldKeysWithoutId.append(filedName)

    def checkFiled(self, filedName: str, filedType: str):
        """
        检测mysql表的字段和py表中的字段是否匹配：
            1、是否存在
            2、类型是否一样
        """
        if filedName == 'id':
            return

        # 删除已存在的字段
        if filedName in self.newFileds:
            del self.newFileds[filedName]

        if filedName in self.filedDict:
            if filedType != str(self.filedDict[filedName]):
                self.excFileds[filedName] = self.filedDict[filedName]
        else:
            # 记录表中有的字段而配置中删掉的字段
            self.delFileds.append(filedName)

    def exec(self, finishCallback):
        """
        执行对表字段的增、删、改
        """
        self.finishCallback = finishCallback
        self.fieldKeys = list(self.filedDict.keys())
        self.fieldKeysStr = ','.join(self.fieldKeys)
        self.fieldKeysStrWithoutId = ','.join(self.fieldKeysWithoutId)

        def callback(result, rows, insertid, error):
            if error:
                ERROR_MSG(f'db::exec create table[{self.tbName}] error: %s' % error)
            else:
                # 虽然每次都会调用到这里，但是除了第一次会创建表，其它时候都不会重新创建表
                DEBUG_MSG('db::exec create table success result: %s, rows: %s, insertid: %i, error: %s' % (str(result), str(rows), insertid, error))
                self.checkExistedFileds()

        KBEngine.executeRawDatabaseCommand(self.sql, callback)

    def checkExistedFileds(self):
        """
        检测表中的字段和配置的字段，根据情况对表进行字段的增删改操作。
        """
        def callback(result, rows, insertid, error):
            if error:
                ERROR_MSG('db::checkExistedFileds error: %s' % error)
            else:
                DEBUG_MSG('db::checkExistedFileds result: %s' % (str(result)))

                size = len(result)
                for i in range(1, size, 1):  # 注意这里忽略了id这个字段的检测
                    item = result[i]  # [b'dbid', b'bigint(20) unsigned', b'NO', b'', b'0', b'']
                    filedName = str(item[0], 'utf-8')  # 二进制转字符串必须指定编码
                    filedType = str(item[1], 'utf-8')
                    notNo = 'not null' if str(item[2], 'utf-8') == 'NO' else ''
                    default = str(item[4], 'utf-8')

                    if len(notNo) > 0:
                        if 'varchar' in filedType:
                            default = '""'
                        fullFiledType = '%s %s default %s' % (filedType, notNo, default)
                    else:
                        fullFiledType = filedType

                    self.checkFiled(filedName, fullFiledType)

                self._updateTable()

        sql = 'desc %s;' % self.tbName
        KBEngine.executeRawDatabaseCommand(sql, callback)

    def _updateTable(self):
        """
        更新表结构
        """
        def _execSQL(sql, callback):
            """
            执行新增字段、删除字段、更新字段类型
            """
            DEBUG_MSG('db::execSQL sql: %s ' % (sql))

            def sqlCallback(result, rows, insertid, error):
                if error:
                    ERROR_MSG('db::execSQL error: %s' % error)
                else:
                    DEBUG_MSG('db::execSQL success')
                    if callback is not None:
                        callback()

            KBEngine.executeRawDatabaseCommand(sql, sqlCallback)

        def _deleteFiled():
            delSql = self.delFiledSql
            if len(delSql) > 0:
                _execSQL(delSql, _changeFiled)
            else:
                _changeFiled()

        def _changeFiled():
            excSql = self.chaFiledSql
            if len(excSql) > 0:
                _execSQL(excSql, _addFiled)
            else:
                _addFiled()

        def _addFiled():
            newSql = self.newFiledSql
            if len(newSql) > 0:
                _execSQL(newSql, self.finishCallback)
            else:
                if self.finishCallback is not None:
                    self.finishCallback()

        # 先删除字段
        _deleteFiled()

    def addLine(self, line, success_callback=None):
        """
        执行存表操作：添加行，新添加行其自增id还是为0，所以不要用id作为唯一key管理数据。
        """
        def callback(result, rows, insertid, error):
            if error:
                ERROR_MSG('addLine error: %s' % error)
            else:
                DEBUG_MSG('addLine success')
                if success_callback is not None:
                    success_callback()

        values = []
        for key in self.fieldKeysWithoutId:
            if key not in line:
                ERROR_MSG(f'addLine error not found key: {key}')
                return
            values.append(self.filedDict[key].valToStr(line[key]))

        if len(values) == 0:
            ERROR_MSG('addLine values is zero')
            return

        sql = f'insert into {self.tbName} ({self.fieldKeysStrWithoutId}) values ({",".join(values)});'
        DEBUG_MSG(f'addLine sql: {sql}')
        KBEngine.executeRawDatabaseCommand(sql, callback)

    def delLine(self, condition, success_callback=None):
        def callback(result, rows, insertid, error):
            if error:
                ERROR_MSG(f'delLine error: {error}  sql: {sql}')
            else:
                DEBUG_MSG(f'delLine success {sql}')
                if success_callback is not None:
                    success_callback()
        sql = f'delete from {self.tbName} where {condition};'
        DEBUG_MSG(f'addLine sql: {sql}')
        KBEngine.executeRawDatabaseCommand(sql, callback)

    def updateLine(self, line, condition: str, success_callback=None):
        values = []
        for key in line:
            if key in self.filedDict:
                value_str = self.filedDict[key].valToStr(line[key])
                values.append(f'{key}={value_str}')

        sql = f'update {self.tbName} set {",".join(values)} where {condition};'

        def callback(result, rows, insertid, error):
            if error:
                ERROR_MSG(f'updateLine error: {error}  sql: {sql}')
            else:
                DEBUG_MSG(f'updateLine success {sql}')
                if success_callback is not None:
                    success_callback()
        KBEngine.executeRawDatabaseCommand(sql, callback)

    def loadAll(self, condition, success_callback):
        def callback(result, rows, insertid, error):
            if error:
                ERROR_MSG(f'loadAll failed: {error}')
            else:
                DEBUG_MSG(f'loadAll success {len(result)}')
                result_lst = []
                for line in result:
                    item = {}
                    for i in range(len(line)):
                        file_name = self.fieldKeysWithoutId[i]
                        item[file_name] = self.filedDict[file_name].convertFunc(line[i])
                    result_lst.append(item)
                DEBUG_MSG(f'loadAll result_lst: {len(result_lst)}')
                if success_callback is not None:
                    success_callback(result_lst)
        sql = f'select {self.fieldKeysStrWithoutId} from {self.tbName} where {condition};'
        DEBUG_MSG(f'loadAll sql: {sql}')
        KBEngine.executeRawDatabaseCommand(sql, callback)

    def clearTable(self, success_callback):
        def callback(result, rows, insertid, error):
            if error:
                ERROR_MSG(f'clear table {self.tbName} failed: {error}')
            else:
                DEBUG_MSG(f'clear table {self.tbName} success')
                if success_callback is not None:
                    success_callback(result)
        
        sql = f'truncate table {self.tbName}'
        KBEngine.executeRawDatabaseCommand(sql, callback)